<?php

static $config = NULL;
static $log = NULL;

// Error Handler
function error_handler_for_export($errno, $errstr, $errfile, $errline) {
	global $config;
	global $log;
	
	switch ($errno) {
		case E_NOTICE:
		case E_USER_NOTICE:
			$errors = "Notice";
			break;
		case E_WARNING:
		case E_USER_WARNING:
			$errors = "Warning";
			break;
		case E_ERROR:
		case E_USER_ERROR:
			$errors = "Fatal Error";
			break;
		default:
			$errors = "Unknown";
			break;
	}
		
	if (($errors=='Warning') || ($errors=='Unknown')) {
		return true;
	}

	if ($config->get('config_error_display')) {
		echo '<b>' . $errors . '</b>: ' . $errstr . ' in <b>' . $errfile . '</b> on line <b>' . $errline . '</b>';
	}
	
	if ($config->get('config_error_log')) {
		$log->write('PHP ' . $errors . ':  ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
	}

	return true;
}


function fatal_error_shutdown_handler_for_export()
{
	$last_error = error_get_last();
	if ($last_error['type'] === E_ERROR) {
		// fatal error
		error_handler_for_export(E_ERROR, $last_error['message'], $last_error['file'], $last_error['line']);
	}
}


class ModelToolExport extends Model {


	function clean( &$str, $allowBlanks=FALSE ) {
		$result = "";
		$n = strlen( $str );
		for ($m=0; $m<$n; $m++) {
			$ch = substr( $str, $m, 1 );
			if (($ch==" ") && (!$allowBlanks) || ($ch=="\n") || ($ch=="\r") || ($ch=="\t") || ($ch=="\0") || ($ch=="\x0B")) {
				continue;
			}
			$result .= $ch;
		}
		return $result;
	}


	function import( &$database, $sql ) {
		foreach (explode(";\n", $sql) as $sql) {
			$sql = trim($sql);
			if ($sql) {
				$database->query($sql);
			}
		}
	}


	protected function getDefaultLanguageId( &$database ) {
		$code = $this->config->get('config_language');
		$sql = "SELECT language_id FROM `".DB_PREFIX."language` WHERE code = '$code'";
		$result = $database->query( $sql );
		$languageId = 1;
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$languageId = $row['language_id'];
				break;
			}
		}
		return $languageId;
	}


        function populateCrmCustomerWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('first_lastname'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('phonemobile'),20)+1);
                $worksheet->setColumn($j,$j++,max(strlen('adress'),40)+1);
                $worksheet->setColumn($j,$j++,max(strlen('birthday'),20)+1);
                $worksheet->setColumn($j,$j++,max(strlen('point'),20)+1);
                $worksheet->setColumn($j,$j++,max(strlen('email_address'),20)+1);
		
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Họ Tên', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Số Điện Thoại', $boxFormat );
                $worksheet->writeString( $i, $j++, 'Địa Chỉ', $boxFormat );
                $worksheet->writeString( $i, $j++, 'Ngày Sinh', $boxFormat );
                $worksheet->writeString( $i, $j++, 'Điểm Thưởng', $boxFormat );
                $worksheet->writeString( $i, $j++, 'Email', $boxFormat );
		
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                
                $sql = "SELECT c.*,e.email_address FROM " . DB_PREFIX . "crm_customer c, email_addresses e,email_addr_bean_rel r";
			
                        
                        $sql .= " WHERE 1"; 
                        $sql.=" and c.id=r.bean_id and r.email_address_id= e.id";
                         if (isset($_GET['id'])) {
				$sql .= " AND  c.id='" . $_GET['id'] . "'";
			}
                        if (isset($_GET['day'])) {
				$sql .= " AND  date_format(birthday,'%d')='" . $_GET['day'] . "'";
			}
                        
                        // month
                        if (isset($_GET['month']) ) {
				$sql .= " AND  date_format(birthday,'%m')='" . $_GET['month'] . "'";
			}
                        //year
                         if (isset($_GET['year'])) {
				$sql .= " AND  date_format(birthday,'%Y')='" .$_GET['year'] . "'";
			}
                        if (isset($_GET['day'])) {
				$sql .= " AND  date_format(birthday,'%d')='" . $_GET['day'] . "'";
			}
                        if(isset($_GET['email'])){
                            $sql .=  " AND LCASE(email_address) LIKE '" . $_GET['email']  . "%'";
                        }
                        if (isset($_GET['district'])) {
				$sql .= " AND primary_address_street LIKE '" .  $_GET['district'] . "%'";
			}
                        if (isset($_GET['city'])) {
				$sql .= " AND primary_address_city LIKE '" . $_GET['city'] . "%'";
			}
			if (isset($_GET['last_name'])) {
				$sql .= " AND LCASE(last_name) LIKE '" . $this->db->escape(utf8_strtolower($_GET['last_name'])) . "%'";
			}
                        
			if (!empty($_GET['first_name'])) {
				$sql .= " AND LCASE(first_name) LIKE '" . $this->db->escape(utf8_strtolower($_GET['first_name'])) . "%'";
			}
			
			if (!empty($_GET['phonemobile'])) {
				$sql .= " AND phone_mobile LIKE '" . $this->db->escape($_GET['phonemobile']) . "%'";
			}
                        if (!empty($_GET['birthday'])) {
				$sql .= " AND birthday LIKE '" . $this->db->escape($_GET['birthday']) . "%'";
			}
			
			if (isset($_GET['point']) && !is_null($_GET['point'])) {
				$sql .= " AND points = '" . $this->db->escape($_GET['point']) . "'";
			}								
			
                        
			if (isset($_GET['sort'])) {
				$sql .= " ORDER BY " . $_GET['sort'];	
			} else {
				$sql .= " ORDER BY last_name";	
			}
			
			if (isset($_GET['order']) && ($_GET['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
		//$query  = "SELECT first_name,last_name,phone_mobile,primary_address_street,primary_address_city,primary_address_state, primary_address_country ,birthday, points FROM `crm_customer` ";
                
                
                
		$c=1;
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['last_name']."  ". $row['first_name']);
			$worksheet->write( $i, $j++, $row['phone_mobile'] );
                        $worksheet->write( $i, $j++,$row['alt_address_street'].",  ". $row['primary_address_street'].", ". $row['primary_address_city'].",".$row['primary_address_country']);
                        $worksheet->write( $i, $j++, $row['birthday'] );
                        $worksheet->write( $i, $j++, $row['points'] );
                        $worksheet->write( $i, $j++, $row['email_address'] );
			
			$i += 1;
			$j = 0;
                        $c++;
		}
	}

        function populateCrmGoodGroup2Worksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('goodgroup2'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('amount'),20)+1);
                
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Tên Nhóm Hàng', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Doanh Thu', $boxFormat );
               
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                
                
                	$sql = "SELECT crm_sec_industry.id as id_group_2, crm_sec_industry.name, SUM( crm_customer_transactions.amount ) AS amount
FROM crm_goods, crm_customer_transactions, crm_customer_transactions_crm_goods_c, crm_customer_bills, crm_customer_transactions_crm_customer_bills_c, crm_industry, crm_goods_crm_industry_c, crm_sec_industry, crm_industry_crm_sec_industry_c
WHERE 
crm_industry.id = crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_industry_idb
and
crm_sec_industry.id = crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_sec_industry_ida
and
crm_goods.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_goods_idb
and
crm_industry.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_industry_ida
and
crm_goods.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_goods_ida
AND crm_customer_transactions.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_customer_transactions_idb
AND crm_customer_transactions.id = crm_customer_transactions_crm_customer_bills_c.crm_customaf9eactions_idb
AND crm_customer_bills.id = crm_customer_transactions_crm_customer_bills_c.crm_custom19b9r_bills_ida";

                        if (!empty($_GET['filter_date_start'])) {

                        $sql.= " AND DATE(crm_customer_bills.bill_date) >= '" . $this->db->escape($_GET['filter_date_start']) . "'";
                                                }
                        if (!empty($_GET['filter_date_end'])) {

                        $sql.=" AND DATE(crm_customer_bills.bill_date) <= '" . $this->db->escape($_GET['filter_date_end']) . "'";
                                                }
                        
                        $sql.=" GROUP BY crm_industry.name ";

                
		$c=1;
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['name']);
			$worksheet->write( $i, $j++, $row['amount']." VND" );
                        
			$i += 1;
			$j = 0;
                        $c++;
		}
	}
        function populateCrmGoodGroup3Worksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('goodgroup2'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('amount'),20)+1);
                
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Tên Nhóm Hàng', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Doanh Thu', $boxFormat );
               
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                
                
                  $sql = "SELECT crm_industry.id as id_group_3, crm_industry.name as name, SUM( crm_customer_transactions.amount ) AS amount
                FROM crm_goods, crm_customer_transactions, crm_customer_transactions_crm_goods_c, crm_industry, 
                crm_goods_crm_industry_c, crm_industry_crm_sec_industry_c, crm_customer_bills, crm_customer_transactions_crm_customer_bills_c
                WHERE crm_industry.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_industry_ida
                AND crm_goods.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_goods_idb
                AND (
                crm_goods.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_goods_ida
                )
                AND (
                crm_customer_transactions.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_customer_transactions_idb
                )
                AND crm_customer_transactions.id = crm_customer_transactions_crm_customer_bills_c.crm_customaf9eactions_idb
                AND crm_customer_bills.id = crm_customer_transactions_crm_customer_bills_c.crm_custom19b9r_bills_ida
                and crm_industry.id = crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_industry_idb ";
            if (!empty($_GET['id_group_2'])) {
                $sql .= "and crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_sec_industry_ida ='". $this->db->escape($_GET['id_group_2'])."'";
            }
            if (!empty($_GET['filter_date_start'])) {
			$sql .= " AND DATE(crm_customer_bills.bill_date) >= '" . $this->db->escape($_GET['filter_date_start']) . "'";
		}

		if (!empty($_GET['filter_date_end'])) {
			$sql .= " AND DATE(crm_customer_bills.bill_date) <= '" . $this->db->escape($_GET['filter_date_end']) . "'";
		}
                $sql .= " GROUP BY crm_industry.name
                ORDER BY SUM( crm_customer_transactions.amount ) DESC 
                ";

                
		$c=1;
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['name']);
			$worksheet->write( $i, $j++, $row['amount']." VND" );
                        
			$i += 1;
			$j = 0;
                        $c++;
		}
	}
 
        
        function populateCrmMonthReportWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('month'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('amount'),20)+1);
                
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Tháng', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Doanh Thu', $boxFormat );
               
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                
            $sql = "SELECT crm_customer_transactions.id, MONTH( bill_date ) AS name, SUM( crm_customer_transactions.amount ) AS amount\n
                    FROM crm_customer_transactions, crm_customer_bills, crm_customer_transactions_crm_customer_bills_c\n
                    WHERE crm_customer_bills.id = crm_customer_transactions_crm_customer_bills_c.crm_custom19b9r_bills_ida\n
                    AND crm_customer_transactions.id = crm_customer_transactions_crm_customer_bills_c.crm_customaf9eactions_idb\n
                    AND YEAR( bill_date ) = '";
                    if (!empty($_GET['filter_group'])) {
                                $sql .= $this->db->escape($_GET['filter_group']) . "'\n";

                        }
                    $sql .= " GROUP BY MONTH( bill_date )";
                
		$c=1;
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['name']);
			$worksheet->write( $i, $j++, $row['amount']." VND" );
                        
			$i += 1;
			$j = 0;
                        $c++;
		}
	}
 
        
        
          function populateCrmRegisterCustomerWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('month'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('amount'),20)+1);
                
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Tháng', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Số Lượng', $boxFormat );
               
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                
         $sql = "SELECT result.id, result.name, COUNT( result.id ) AS count
                        FROM (

                        SELECT crm_customer.id AS id, MONTH( crm_card.date_entered ) AS name
                        FROM crm_customer, crm_card, crm_card_crm_customer_c
                        WHERE crm_customer.id = crm_card_crm_customer_c.crm_card_crm_customercrm_customer_ida
                        AND crm_card.id = crm_card_crm_customer_c.crm_card_crm_customercrm_card_idb
                        AND YEAR( crm_card.date_entered ) = '";
                if (!empty($_GET['filter_group'])) {
                                $sql .= $this->db->escape($_GET['filter_group']) . "'";

                        }
                        $sql .= "GROUP BY MONTH( crm_card.date_entered )
                        )result
                        GROUP BY result.name  ";       
		$c=1;
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['name']);
			$worksheet->write( $i, $j++, $row['count'] );
                        
			$i += 1;
			$j = 0;
                        $c++;
		}
	}
 
        
        function populateCrmGoodsWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('goodgroup2'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('amount'),20)+1);
                
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Tên Hàng', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Doanh Thu', $boxFormat );
               
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                
             $sql = "SELECT crm_goods.id as id_group_3, crm_goods.name, SUM( crm_customer_transactions.amount ) AS amount
                FROM crm_goods, crm_customer_transactions, crm_customer_transactions_crm_goods_c, crm_customer_bills, crm_customer_transactions_crm_customer_bills_c, crm_industry, crm_goods_crm_industry_c, crm_industry_crm_sec_industry_c
                WHERE 
                crm_goods.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_goods_idb
                AND
                crm_industry.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_industry_ida
                AND
                crm_goods.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_goods_ida
                AND crm_customer_transactions.id = crm_customer_transactions_crm_goods_c.crm_customer_transactions_crm_goodscrm_customer_transactions_idb
                AND crm_customer_transactions.id = crm_customer_transactions_crm_customer_bills_c.crm_customaf9eactions_idb
                AND crm_customer_bills.id = crm_customer_transactions_crm_customer_bills_c.crm_custom19b9r_bills_ida

                AND
                crm_industry.id = crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_industry_idb
                AND
                crm_goods.id = crm_goods_crm_industry_c.crm_goods_crm_industrycrm_goods_idb ";
                if (!empty($_GET['id_group_2'])) {
                $sql .= " AND crm_industry_crm_sec_industry_c.crm_industry_crm_sec_industrycrm_sec_industry_ida = '". $this->db->escape($_GET['id_group_2'])."' ";
            }
            if (!empty($_GET['id_group_3'])) {
                $sql .= " AND crm_goods_crm_industry_c.crm_goods_crm_industrycrm_industry_ida = '". $this->db->escape($_GET['id_group_3'])."' ";
            }
            if (!empty($_GET['filter_date_start'])) {
			$sql .= " AND DATE(crm_customer_bills.bill_date) >= '" . $this->db->escape($_GET['filter_date_start']) . "' ";
		}

		if (!empty($_GET['filter_date_end'])) {
			$sql .= " AND DATE(crm_customer_bills.bill_date) <= '" . $this->db->escape($_GET['filter_date_end']) . "' ";
		}
                $sql .= " GROUP BY crm_goods.id
                ORDER BY SUM( crm_customer_transactions.amount ) DESC  ";

                
		$c=1;
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['name']);
			$worksheet->write( $i, $j++, $row['amount']." VND" );
                        
			$i += 1;
			$j = 0;
                        $c++;

                }
                
                }

        function populateCrmInvoiceWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('nameinvoice'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('dateenter'),20)+1);
                $worksheet->setColumn($j,$j++,max(strlen('amount'),40)+1);
                $worksheet->setColumn($j,$j++,max(strlen('point'),20)+1);
               
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Hóa Đơn', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Ngày nhập', $boxFormat );
                $worksheet->writeString( $i, $j++, 'Tổng tiền', $boxFormat );
                $worksheet->writeString( $i, $j++, 'Điểm thưởng', $boxFormat );
               
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                $id = $_GET['id'];
                 
                $sql = "select b.* from crm_customer_bills b, crm_customer_bills_crm_customer_c r where b.id = r.crm_customer_bills_crm_customercrm_customer_bills_idb and r.crm_customer_bills_crm_customercrm_customer_ida= '$id' ";
                
                
                 if(isset($_GET['bill'])){
                     $sql .= " AND b.name LIKE '".$_GET['bill'] ."%'";
                 }
                 if(isset($_GET['date_bill'])){
                     $date_bills = $_GET['date_bill'];
                     
                     
                    
                        $date_bill  = date('Y-m-d',strtotime($date_bills));
                   $sql .= " AND b.bill_date LIKE '".$date_bill."%'";
                 }
		$c=1;
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['name']);
			$worksheet->write( $i, $j++, $row['bill_date'] );
                        $worksheet->write( $i, $j++,$row['amount']);
                        $worksheet->write( $i, $j++, $row['point'] );
                        
			$i += 1;
			$j = 0;
                        $c++;
		}
	}
        function populateCrmDetaileInvoiceWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
    
		$j = 0;
                $worksheet->setColumn($j,$j++,strlen('number')+1);
		$worksheet->setColumn($j,$j++,max(strlen('namegood'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('quantity'),20)+1);
                $worksheet->setColumn($j,$j++,max(strlen('amount'),40)+1);
               
               
		// The heading row
		$i = 0;
		$j = 0;
                $worksheet->writeString( $i, $j++, 'STT', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Tên mặt hàng', $boxFormat );
		$worksheet->writeString( $i, $j++, 'Số lượng', $boxFormat );
                $worksheet->writeString( $i, $j++, 'Tổng tiền', $boxFormat );
             
               
		// The actual product specials data
		$i += 1;
		$j = 0;
                
                //  lenh query
                $id = $_GET['billid'];
            
                
            $bill= array();
            
            $query = $this->db->query("select crm_customaf9eactions_idb from crm_customer_transactions_crm_customer_bills_c where crm_custom19b9r_bills_ida='$id'" );
            
            $query1 = $query->rows;
            
           
            for($i=0;$i<count($query1);$i++)
            {
               $id = $query1[$i]['crm_customaf9eactions_idb'];
                 $query = $this->db->query("SELECT * FROM `crm_customer_transactions` WHERE id ='$id'" );
                $query3 = $query->rows;  
                
               
                
                  $query = $this->db->query("SELECT name
FROM  `crm_goods` 
WHERE id = ( 
SELECT crm_customer_transactions_crm_goodscrm_goods_ida
FROM crm_customer_transactions_crm_goods_c
WHERE crm_customer_transactions_crm_goodscrm_customer_transactions_idb =  '$id' )" );
                $query4 = $query->rows; 
               // print_r($query4);
                  $bill[$i] = array(
                 'name'     => $query4[0]['name'],
                 'amount'   => $query3[0]['amount'],
                 'quantity' => $query3[0]['quantity'],
                );
              
            
            }
           
                
                
		$c=1;
		
		foreach ($bill as $row) {
			$worksheet->setRow( $i, 13 );
                        $worksheet->write( $i, $j++, $c);
			$worksheet->write( $i, $j++, $row['name']);
			$worksheet->write( $i, $j++, $row['quantity'] );
                        $worksheet->write( $i, $j++,$row['amount']);
                       
                        
			$i += 1;
			$j = 0;
                        $c++;
		}
	}
	protected function clearSpreadsheetCache() {
		$files = glob(DIR_CACHE . 'Spreadsheet_Excel_Writer' . '*');
		
		if ($files) {
			foreach ($files as $file) {
				if (file_exists($file)) {
					@unlink($file);
					clearstatcache();
				}
			}
		}
	}


        function downloadcustomer() {
		global $config;
		global $log;
		$config = $this->config;
		$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');
		$database =& $this->db;
		$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		chdir( '../system/pear' );
		require_once "Spreadsheet/Excel/Writer.php";
		chdir( '../../admin' );
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('backup_customer.xls');
		
		$worksheet =& $workbook->addWorksheet('Customer');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmCustomerWorksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
		$workbook->close();
		
		// Clear the spreadsheet caches
		$this->clearSpreadsheetCache();
		exit;
	}


            function downloadgoodgroup2() {
		global $config;
		global $log;
		$config = $this->config;
		$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');
		$database =& $this->db;
		$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		chdir( '../system/pear' );
		require_once "Spreadsheet/Excel/Writer.php";
		chdir( '../../admin' );
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('backup_goodgroup2.xls');
		
		$worksheet =& $workbook->addWorksheet('Nhom Hang Cap 2');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmGoodGroup2Worksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
                
               
                
              
		$workbook->close();
		
		// Clear the spreadsheet caches
		$this->clearSpreadsheetCache();
		exit;
	}

        
         function downloadregistercustomer() {
		global $config;
		global $log;
		$config = $this->config;
		$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');
		$database =& $this->db;
		$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		chdir( '../system/pear' );
		require_once "Spreadsheet/Excel/Writer.php";
		chdir( '../../admin' );
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('backup_registercustomer.xls');
		
		$worksheet =& $workbook->addWorksheet('Khach hang dang ky theo thang');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmRegisterCustomerWorksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
                
               
                
              
		$workbook->close();
		
		// Clear the spreadsheet caches
		$this->clearSpreadsheetCache();
		exit;
	}

        
        function downloadmonthreport() {
        
		global $config;
		global $log;
		$config = $this->config;
		$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');
		$database =& $this->db;
		$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		chdir( '../system/pear' );
		require_once "Spreadsheet/Excel/Writer.php";
		chdir( '../../admin' );
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('backup_monthreport.xls');
		
		$worksheet =& $workbook->addWorksheet('Bao cao doanh thu theo thang');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmMonthReportWorksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
                
               
                
              
		$workbook->close();
		
		// Clear the spreadsheet caches
		$this->clearSpreadsheetCache();
		exit;
        
        }

        function downloadgoods() {
		global $config;
		global $log;
		$config = $this->config;
		$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');
		$database =& $this->db;
		$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		chdir( '../system/pear' );
		require_once "Spreadsheet/Excel/Writer.php";
		chdir( '../../admin' );
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('backup_goods.xls');
		
		$worksheet =& $workbook->addWorksheet('Danh sach mat hang');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmGoodsWorksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
                
               
                
              
		$workbook->close();
		
		// Clear the spreadsheet caches
		$this->clearSpreadsheetCache();
		exit;
	}

        
        function downloadgoodgroup3() {
		global $config;
		global $log;
		$config = $this->config;
		$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');
		$database =& $this->db;
		$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		chdir( '../system/pear' );
		require_once "Spreadsheet/Excel/Writer.php";
		chdir( '../../admin' );
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('backup_goodgroup3.xls');
		
		$worksheet =& $workbook->addWorksheet('Nhom Hang Cap 3');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmGoodGroup3Worksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
                
               
                
              
		$workbook->close();
		
		// Clear the spreadsheet caches
		$this->clearSpreadsheetCache();
		exit;
	}

        
        function downloaddetailebill() {
		global $config;
		global $log;
		$config = $this->config;
		$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		register_shutdown_function('fatal_error_shutdown_handler_for_export');
		$database =& $this->db;
		$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		chdir( '../system/pear' );
		require_once "Spreadsheet/Excel/Writer.php";
		chdir( '../../admin' );
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('backup_detaileinvoice.xls');
		
		$worksheet =& $workbook->addWorksheet('DetaileInvoice');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmDetaileInvoiceWorksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
                
                $worksheet =& $workbook->addWorksheet('customer');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateCrmCustomerWorksheet( $worksheet, $database, $priceFormat, $boxFormat, $textFormat );
		$worksheet->freezePanes(array(1, 1, 1, 1));
                
              
		$workbook->close();
		
		// Clear the spreadsheet caches
		$this->clearSpreadsheetCache();
		exit;
        }

}

?>